import codecs
import csv
import time

import pandas as pd
import xlwings


class ExcelUtil(object):
    @staticmethod
    def save(rows, xlsx_name='out', encrypt=0):
        '''
        rows: [('id', 'num'), ('1', 'a'), ('2', 'b'), ('3', 'c')]
        xlsx_name: output.1692253217.xlsx
        encrypt: 0-default, 1-no encrypt, other-pwd
        '''

        ts = time.time()
        xlsx_name = f'{xlsx_name}.{int(ts)}.xlsx'

        df = pd.DataFrame(rows)
        df.to_excel(excel_writer=xlsx_name, index=False, header=False)

        if encrypt == 1:  # 不加密
            return
        if encrypt == 0:  # 默认加密 0123
            encrypt = time.strftime('%d%y', time.localtime(ts))
            print(f'PS: {encrypt}')

        app = xlwings.App(visible=False, add_book=False)
        app.books.open(xlsx_name).save(xlsx_name, encrypt)
        app.quit()
        # app.kill()

    @staticmethod
    def xlsx_to_csv(xlsx_name, csv_name):
        app = xlwings.App(visible=True, add_book=False)
        book = app.books.open(xlsx_name)
        sheet = book.sheets[0]

        rows = sheet.range('A1').expand().value
        with codecs.open(csv_name, 'w', encoding='utf-8') as f:
            write = csv.writer(f)
            for row in rows:
                write.writerow(row)

        app.quit()
        app.kill()

    @staticmethod
    def sheet_copy2_book():
        app = xlwings.App()
        book_a = app.books.open('a.xlsx')
        book_b = app.books.open('b.xlsx')

        # copy to
        book_a.sheets[0].copy(after=book_b.sheets[0], name='sheet_2')

        # rename sheet
        book_b.sheets[1].api.name = '2'
        book_b.save()

        app.quit()


if __name__ == '__main__':
    ExcelUtil.xlsx_to_csv('11.xlsx', '11.csv')

    # rnt_list = [('id', 'num'), ('1', 'a'), ('2', 'b'), ('3', 'c')]
    # excel_name = 'out'
    # Excel.save(rnt_list, excel_name, 'abc')
